-- liquibase formatted sql

-- changeset n.parkhomenko:V1695411766_create_table_users

CREATE TABLE users
(
    id          INT         NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    first_name  VARCHAR(30) NOT NULL,
    second_name VARCHAR(30) NOT NULL,
    email       VARCHAR(50) NOT NULL UNIQUE,

    created_at  TIMESTAMP   NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMP            DEFAULT NULL
);

CREATE table user_passwords
(
    id            INT       NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    user_id       INT       NOT NULL UNIQUE,
    password_hash TEXT      NOT NULL,

    created_at    TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at    TIMESTAMP          DEFAULT NULL,

    CONSTRAINT fk_user_password_password FOREIGN KEY (user_id) REFERENCES users (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE user_auth_identities
(
    id         INT       NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    user_id    INT       NOT NULL,
    type       SMALLINT  NOT NULL,
    identity   VARCHAR   NOT NULL,

    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP          DEFAULT NULL,

    CONSTRAINT fk_user_auth_identity_user FOREIGN KEY (user_id) REFERENCES users (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,

    CONSTRAINT user_auth_identity_type_identity_unique UNIQUE (type, identity)
);

